﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace Noris.Schedule.Support.Sql
{
    #region class ParameterCollection : kolekce paramerů do SQL dotazu
    /// <summary>
    /// ParameterCollection : kolekce paramerů do SQL dotazu.
    /// Parametry se přidávají do Listu this.Parameters
    /// </summary>
    public class ParameterCollection
    {
        #region Konstrukce, public property a metody
        /// <summary>
        /// Konstruktor
        /// </summary>
        public ParameterCollection()
        {
            this.Parameters = new List<Parameter>();
        }
        /// <summary>
        /// Příznak, že neexistují žádné parametry
        /// </summary>
        public bool IsEmpty { get { return (this.Parameters.Count == 0); } }
        /// <summary>
        /// Souhrn parametrů
        /// </summary>
        public List<Parameter> Parameters { get; private set; }
        /// <summary>
        /// Vloží danou hodnotu do daného parametru.
        /// 
        /// </summary>
        /// <param name="name"></param>
        /// <param name="value"></param>
        public void SetValue(string name, object value)
        {
            Parameter par = this.FindParameter(name);
            if (par == null)
                throw new KeyNotFoundException("Parameters with name " + name + " does not exists in collection.");

            par.Value = value;
        }
        /// <summary>
        /// Zkusí najít a vrátit hodnotu
        /// </summary>
        /// <param name="name"></param>
        /// <param name="data"></param>
        /// <returns></returns>
        public bool TryGetValue(string name, out object data)
        {
            Parameter par = this.FindParameter(name);
            bool found = (par != null);
            if (found)
                data = par.Value;
            else
                data = null;
            return found;
        }
        /// <summary>
        /// Zkusí najít parametr dle jeho názvu.
        /// Výjimku (ArgumentNullException) hodí, pokud hledané jméno bude null or empty.
        /// Pokud parametr daného jména nenajde, vrátí null (nevyhazuje chybu KeyNotFoundException).
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        public Parameter FindParameter(string name)
        {
            if (String.IsNullOrEmpty(name))
                throw new ArgumentNullException("Name of parameters must be specified, can not be null or empty.");
            Parameter par = this.Parameters.FirstOrDefault(p => p.Name == name);
            if (par == null)
                par = this.Parameters.FirstOrDefault(p => String.Equals(p.Name, name, StringComparison.InvariantCultureIgnoreCase));
            return par;
        }
        #endregion
        #region Předání parametrů do SQL příkazu
        /// <summary>
        /// Do daného SQL příkazu přidá svoje parametry
        /// </summary>
        /// <param name="command"></param>
        public void ApplyToSqlCommand(System.Data.SqlClient.SqlCommand command)
        {
            command.Parameters.Clear();
            foreach (Parameter parameter in this.Parameters)
                parameter.AddToSqlCommandParameter(command.Parameters);
        }
        /// <summary>
        /// Do daného SQL příkazu přidá svoje parametry
        /// </summary>
        /// <param name="command"></param>
        public void ApplyToSqlCommand(DataCommand command)
        {
            command.Parameters.Clear();
            foreach (Parameter parameter in this.Parameters)
                parameter.AddToSqlCommandParameter(command.Parameters);
        }
        #endregion
    }
    #endregion
    #region class Parameter : Popis jednoho parametru do SQL dotazu
    /// <summary>
    /// Parameter : Popis jednoho parametru do SQL dotazu
    /// </summary>
    public class Parameter
    {
        #region Konstruktory
        /// <summary>
        /// Konstruktor
        /// </summary>
        /// <param name="name"></param>
        /// <param name="dataType"></param>
        public Parameter(string name)
        {
            this.Name = name;
            this.DataType = Sql.DataType.NotDefined;
            this.Direction = ParameterDirection.Input;
            this.Value = null;
        }
        /// <summary>
        /// Konstruktor
        /// </summary>
        /// <param name="name"></param>
        /// <param name="dataType"></param>
        public Parameter(string name, object value)
        {
            this.Name = name;
            this.DataType = Sql.DataType.NotDefined;
            this.Direction = ParameterDirection.Input;
            this.Value = value;
        }
        /// <summary>
        /// Konstruktor
        /// </summary>
        /// <param name="name"></param>
        /// <param name="dataType"></param>
        public Parameter(string name, DataType dataType)
        {
            this.Name = name;
            this.DataType = dataType;
            this.Direction = ParameterDirection.Input;
            this.Value = null;
        }
        /// <summary>
        /// Konstruktor
        /// </summary>
        /// <param name="name"></param>
        /// <param name="dataType"></param>
        /// <param name="direction"></param>
        public Parameter(string name, DataType dataType, ParameterDirection direction)
        {
            this.Name = name;
            this.DataType = dataType;
            this.Direction = direction;
            this.Value = null;
        }
        /// <summary>
        /// Konstruktor
        /// </summary>
        /// <param name="name"></param>
        /// <param name="dataType"></param>
        /// <param name="direction"></param>
        /// <param name="value"></param>
        public Parameter(string name, DataType dataType, ParameterDirection direction, object value)
        {
            this.Name = name;
            this.DataType = dataType;
            this.Direction = direction;
            this.Value = value;
        }
        /// <summary>
        /// Vizualizace
        /// </summary>
        /// <returns></returns>
        public override string ToString()
        {
            return this.Name + " [" + this.DataType.ToString() + (this.Size > 0 ? "(" + this.Size.ToString() + ")" : "") + "] = " + (this.Value == null ? "{NULL}" : this.Value.ToString());
        }
        #endregion
        #region Public property
        /// <summary>
        /// Název parametru
        /// </summary>
        public string Name { get; private set; }
        /// <summary>
        /// Datový typ
        /// </summary>
        public DataType DataType { get; set; }
        /// <summary>
        /// Datový typ (SQL)
        /// </summary>
        public SqlDbType SqlDbType { get { return GetSqlDbType(this.DataType); } }
        /// <summary>
        /// Velikost, má význam například u znakových polí
        /// </summary>
        public int Size { get; set; }
        /// <summary>
        /// Směr parametru
        /// </summary>
        public ParameterDirection Direction { get; set; }
        /// <summary>
        /// Hodnota parametru
        /// </summary>
        public object Value { get { return this._Value; } set { this._SetValue(value); } }
        private object _Value;
        private void _SetValue(object value)
        {
            this._Value = value;
        }
        #endregion
        #region Předání parametrů do SQL příkazu
        /// <summary>
        /// Do daného SQL příkazu přidá svoje data
        /// </summary>
        /// <param name="sqlParameterCollection"></param>
        public void AddToSqlCommandParameter(System.Data.SqlClient.SqlParameterCollection sqlParameterCollection)
        {
            System.Data.SqlClient.SqlParameter par = new System.Data.SqlClient.SqlParameter(this.Name, this.SqlDbType);
            if (this.Size > 0)
                par.Size = this.Size;
            par.Value = this.Value;
            par.Direction = this.Direction;
            sqlParameterCollection.Add(par);
        }
        /// <summary>
        /// Vrátí SqlDbType podle daného DataType.
        /// </summary>
        /// <returns></returns>
        private static SqlDbType GetSqlDbType(DataType dataType)
        {
            // Využijeme toho, že zdejší DataType je opsaný SqlDbType včetně hodnoty int, takže konverze nebude těžká:
            return (SqlDbType)((int)dataType);
        }
        #endregion
    }
    // Summary:
    //     Specifies SQL Server-specific data type of a field, property, for use in
    //     a System.Data.SqlClient.SqlParameter.
    public enum DataType
    {
        /// <summary>Nedefinovaná hodnota</summary>
        NotDefined = -1,
        // Summary:
        //     System.Int64. A 64-bit signed integer.
        BigInt = 0,
        //
        // Summary:
        //     System.Array of type System.Byte. A fixed-length stream of binary data ranging
        //     between 1 and 8,000 bytes.
        Binary = 1,
        //
        // Summary:
        //     System.Boolean. An unsigned numeric value that can be 0, 1, or null.
        Bit = 2,
        //
        // Summary:
        //     System.String. A fixed-length stream of non-Unicode characters ranging between
        //     1 and 8,000 characters.
        Char = 3,
        //
        // Summary:
        //     System.DateTime. Date and time data ranging in value from January 1, 1753
        //     to December 31, 9999 to an accuracy of 3.33 milliseconds.
        DateTime = 4,
        //
        // Summary:
        //     System.Decimal. A fixed precision and scale numeric value between -10 38
        //     -1 and 10 38 -1.
        Decimal = 5,
        //
        // Summary:
        //     System.Double. A floating point number within the range of -1.79E +308 through
        //     1.79E +308.
        Float = 6,
        //
        // Summary:
        //     System.Array of type System.Byte. A variable-length stream of binary data
        //     ranging from 0 to 2 31 -1 (or 2,147,483,647) bytes.
        Image = 7,
        //
        // Summary:
        //     System.Int32. A 32-bit signed integer.
        Int = 8,
        //
        // Summary:
        //     System.Decimal. A currency value ranging from -2 63 (or -9,223,372,036,854,775,808)
        //     to 2 63 -1 (or +9,223,372,036,854,775,807) with an accuracy to a ten-thousandth
        //     of a currency unit.
        Money = 9,
        //
        // Summary:
        //     System.String. A fixed-length stream of Unicode characters ranging between
        //     1 and 4,000 characters.
        NChar = 10,
        //
        // Summary:
        //     System.String. A variable-length stream of Unicode data with a maximum length
        //     of 2 30 - 1 (or 1,073,741,823) characters.
        NText = 11,
        //
        // Summary:
        //     System.String. A variable-length stream of Unicode characters ranging between
        //     1 and 4,000 characters. Implicit conversion fails if the string is greater
        //     than 4,000 characters. Explicitly set the object when working with strings
        //     longer than 4,000 characters.
        NVarChar = 12,
        //
        // Summary:
        //     System.Single. A floating point number within the range of -3.40E +38 through
        //     3.40E +38.
        Real = 13,
        //
        // Summary:
        //     System.Guid. A globally unique identifier (or GUID).
        UniqueIdentifier = 14,
        //
        // Summary:
        //     System.DateTime. Date and time data ranging in value from January 1, 1900
        //     to June 6, 2079 to an accuracy of one minute.
        SmallDateTime = 15,
        //
        // Summary:
        //     System.Int16. A 16-bit signed integer.
        SmallInt = 16,
        //
        // Summary:
        //     System.Decimal. A currency value ranging from -214,748.3648 to +214,748.3647
        //     with an accuracy to a ten-thousandth of a currency unit.
        SmallMoney = 17,
        //
        // Summary:
        //     System.String. A variable-length stream of non-Unicode data with a maximum
        //     length of 2 31 -1 (or 2,147,483,647) characters.
        Text = 18,
        //
        // Summary:
        //     System.Array of type System.Byte. Automatically generated binary numbers,
        //     which are guaranteed to be unique within a database. timestamp is used typically
        //     as a mechanism for version-stamping table rows. The storage size is 8 bytes.
        Timestamp = 19,
        //
        // Summary:
        //     System.Byte. An 8-bit unsigned integer.
        TinyInt = 20,
        //
        // Summary:
        //     System.Array of type System.Byte. A variable-length stream of binary data
        //     ranging between 1 and 8,000 bytes. Implicit conversion fails if the byte
        //     array is greater than 8,000 bytes. Explicitly set the object when working
        //     with byte arrays larger than 8,000 bytes.
        VarBinary = 21,
        //
        // Summary:
        //     System.String. A variable-length stream of non-Unicode characters ranging
        //     between 1 and 8,000 characters.
        VarChar = 22,
        //
        // Summary:
        //     System.Object. A special data type that can contain numeric, string, binary,
        //     or date data as well as the SQL Server values Empty and Null, which is assumed
        //     if no other type is declared.
        Variant = 23,
        //
        // Summary:
        //     An XML value. Obtain the XML as a string using the System.Data.SqlClient.SqlDataReader.GetValue(System.Int32)
        //     method or System.Data.SqlTypes.SqlXml.Value property, or as an System.Xml.XmlReader
        //     by calling the System.Data.SqlTypes.SqlXml.CreateReader() method.
        Xml = 25,
        //
        // Summary:
        //     A SQL Server 2005 user-defined type (UDT).
        Udt = 29,
        //
        // Summary:
        //     A special data type for specifying structured data contained in table-valued
        //     parameters.
        Structured = 30,
        //
        // Summary:
        //     Date data ranging in value from January 1,1 AD through December 31, 9999
        //     AD.
        Date = 31,
        //
        // Summary:
        //     Time data based on a 24-hour clock. Time value range is 00:00:00 through
        //     23:59:59.9999999 with an accuracy of 100 nanoseconds. Corresponds to a SQL
        //     Server time value.
        Time = 32,
        //
        // Summary:
        //     Date and time data. Date value range is from January 1,1 AD through December
        //     31, 9999 AD. Time value range is 00:00:00 through 23:59:59.9999999 with an
        //     accuracy of 100 nanoseconds.
        DateTime2 = 33,
        //
        // Summary:
        //     Date and time data with time zone awareness. Date value range is from January
        //     1,1 AD through December 31, 9999 AD. Time value range is 00:00:00 through
        //     23:59:59.9999999 with an accuracy of 100 nanoseconds. Time zone value range
        //     is -14:00 through +14:00.
        DateTimeOffset = 34,
    }
    #endregion
}
